﻿create proc [dbo].[getDailyPayData] (@startDate datetime)
as 
begin 
  declare @endDate datetime 
  select @endDate = GETDATE()
      
  if exists(select * from tempdb..sysobjects where  name  like N'%#dateTable%' and type='U')
  drop table #dateTable 
  
  create table #dateTable ([date] varchar(10))        
  
  declare @dtTemp as datetime 
  select @dtTemp = @startDate   
  while (@dtTemp <= @endDate)
  begin 
	insert into #dateTable([date]) select Convert(varchar,@dtTemp,23)
	select @dtTemp =DATEADD(day,1, @dtTemp)
  end 
  
  select a.[date],b.AddCard as AddCard,
        coalesce(c.apptCnt,0) as apptCnt,coalesce(c.apptSum,0.0) as apptSum,
		coalesce(d.regCnt,0) as regCnt,coalesce(d.regSum,0.0) as regSum,
		(coalesce(c.apptCnt,0)+ coalesce(d.regCnt,0)) as sumRegCnt,
		(coalesce(c.apptSum,0.0)+ coalesce(d.regSum,0.0)) as sumRegFee,
		coalesce(e.payCnt,0) as TotalCnt,coalesce(e.payFee,0.0) as TotaltSum,
		coalesce(f.PreCnt,0) as PreCnt,coalesce(f.PrepayMoney ,0.0) as PrepayMoney,
		(coalesce(c.apptSum,0.0)+ coalesce(d.regSum,0.0)+coalesce(f.PrepayMoney ,0.0)+coalesce(e.payFee,0.0)) as sumFee,
		coalesce(e.YiBaoCnt,0) as YiBaoCnt,coalesce(e.YiBaoMoney,0.0) as YiBaoMoney,
		coalesce(e.YibaoPersonMoney,0.0) as YibaoPersonMoney,
        coalesce(e.YibaoPersonMoney,0.0) as  YiBaoPrescMoney,
        (coalesce(c.apptSum,0.0)+ coalesce(d.regSum,0.0)+coalesce(f.PrepayMoney ,0.0)+coalesce(e.payFee,0.0)-coalesce(e.YiBaoMoney,0.0))as pureSumFee 

  from #dateTable a left join 
  (
  -- 绑卡
   select CONVERT(varchar,b.AddDt,23)as [date],COUNT(distinct b.CardNo) as AddCard from HospCard b 
  where  b.AddDt>@StartDate 
  group by CONVERT(varchar,b.AddDt,23) 
  )b on a.date =b.date
  left join
  ( 
  --预约
  select CONVERT(varchar,createtime,23) as [Date],COUNT(*) as apptCnt ,sum(SumFee) as apptSum from ApptOrder 
  where PayState = 1 and CreateTime >@StartDate and CONVERT(varchar,createtime,23) <> RegDate 
  group by CONVERT(varchar,createtime,23)
  ) c on a.date = c.date   
  left join 
  (
  --挂号
select CONVERT(varchar,createtime,23) as [Date],COUNT(*) as regCnt ,sum(SumFee) as regSum from ApptOrder 
where PayState = 1 and CreateTime >@StartDate and CONVERT(varchar,createtime,23) = RegDate 
group by CONVERT(varchar,createtime,23)
  ) d on a.date = d.date
  left join 
  (
   --门诊缴费 
  select CONVERT(varchar,createtime,23) as [Date],COUNT(*) as payCnt,sum(PrescMoney) as payFee,
  sum (case when YiBaoJydjh is null then  0 else 1 end) as YiBaoCnt,
  SUM(isnull(YibaoMoney,0))as YiBaoMoney ,SUM(isnull(PersonalMoney,0)
  ) as YibaoPersonMoney,
  SUM(isnull(PersonalMoney,0))+SUM(isnull(YibaoMoney,0)
  ) as YiBaoPrescMoney from BillOrder 
  where PayState = 1 and OrderStatus = 8 and  CreateTime >@StartDate
  group by CONVERT(varchar,createtime,23)
  ) e on a.date = e.date
  left join
  (
-- 预缴金 
  select CONVERT(varchar,s2.payTime,23)as [date],count(*) PreCnt,sum(coalesce(s2.PrepayMoney,0)) as PrepayMoney from InpatientPrepayOrder s2 
  where s2.PayState=1 and s2.OrderStatus<>7 and  s2.payTime>@StartDate 
  group by CONVERT(varchar,s2.payTime,23) 
  )f on a.date = f.date 
  
  order by a.[date]
    
  if exists(select * from tempdb..sysobjects where  name  like N'%#dateTable%' and type='U')
  drop table #dateTable 
  
end
